package com.lukebang.util.export.excel;

import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * 
 * ExcelUtil 下午5:23:15 Copyright HEXUNz Inc. All right reserved. Email :
 * alldios@outlook.com
 * 
 */
public class ExcelUtil {
	/** 标题 */
	private static String title = "no title";
	/** 工作页名称 */
	private static String sheetName = "sheet1";

	public static void main(String[] args) throws Exception {
	}

	public static List<ExcelDto> readXls(String url) throws IOException {
		// 读取一个文件
		InputStream is = new FileInputStream(url);
		// 生成 HSSFWorkbook 文件
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		List<ExcelDto> list = new ArrayList<ExcelDto>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 循环行Row
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				// 获取当前总列数
				short cellNumCount = hssfRow.getLastCellNum();
				// 创建对应的ExcelRow 数据对象
				ExcelDto excelRowDto = new ExcelDto(cellNumCount);
				// 获取当前的 行 Cell 的所有列 Row 数据
				for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) {
					HSSFCell cellData = hssfRow.getCell(cellNum);
					if (cellData == null) {
						continue;
					}
					// 将excel数据赋值给相关对象
					excelRowDto.getData()[cellNum] = getValue(cellData);
				}
				list.add(excelRowDto);
			}
		}
		hssfWorkbook.close();
		return list;
	}

	/**
	 * 得到Excel表中的值
	 * 
	 * @param hssfCell
	 *            Excel中的每一个格子
	 * @return Excel中每一个格子中的值
	 */
	public static String getValue(HSSFCell hssfCell) {
		return hssfCell.getStringCellValue();
	}

	/**
	 * 
	 * @param xls
	 *            ExcelDto实体类的一个对象
	 * @throws Exception
	 *             在导入Excel的过程中抛出异常
	 */
	public static String xlsDto2Excel(List<ExcelDto> xls, HttpServletResponse response) throws Exception {
		// 创建Excel文档
		HSSFWorkbook hwb = new HSSFWorkbook();
		ExcelDto xlsDto = null;
		// sheet 对应一个工作页
		HSSFSheet sheet = hwb.createSheet(sheetName);
		HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
		// 初始化 title
		String[] titles = title.split("\\|");
		HSSFCellStyle cellStyle = hwb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setBorderLeft(BorderStyle.valueOf((short) 1));
		cellStyle.setBorderRight(BorderStyle.valueOf((short) 1));
		cellStyle.setBorderBottom(BorderStyle.valueOf((short) 1));
		cellStyle.setBorderTop(BorderStyle.valueOf((short) 1));

		HSSFCellStyle titleType = hwb.createCellStyle();
		HSSFFont f = hwb.createFont();
		f.setBold(true);
		titleType.setFont(f);
		titleType.setAlignment(HorizontalAlignment.CENTER);
		titleType.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		titleType.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		titleType.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleType.setFillBackgroundColor(HSSFColor.YELLOW.index);
		titleType.setBorderLeft(BorderStyle.valueOf((short) 1));
		titleType.setBorderRight(BorderStyle.valueOf((short) 1));
		titleType.setBorderBottom(BorderStyle.valueOf((short) 1));
		titleType.setBorderTop(BorderStyle.valueOf((short) 1));
		HSSFCell[] firstcell = new HSSFCell[titles.length];
		for (int j = 0; j < titles.length; j++) {
			// 设置Excel中的标题
			if (titles[j] != null) {
				// 如果是地址栏的话 加大选项
				if (j == 8) {
					sheet.setColumnWidth(j, 256 * 60 + 184);
				} else {
					sheet.setColumnWidth(j, 20 * 256);
				}
				firstrow.setHeight((short) 600);
				firstcell[j] = firstrow.createCell(j);
				firstcell[j].setCellStyle(titleType);
				firstcell[j].setCellValue(new HSSFRichTextString(titles[j]));
			}
		}
		for (int i = 0; i < xls.size(); i++) {
			// 创建一行
			HSSFRow row = sheet.createRow(i + 1);
			// 得到要插入的每一条记录
			xlsDto = xls.get(i);
			// 遍历数据并存入
			for (int colu = 0; colu <= xls.size(); colu++) {
				// 在一行内循环
				for (int cellNum = 0; cellNum < xlsDto.getData().length; cellNum++) {
					HSSFCell xh = row.createCell(cellNum);
					if (xlsDto.getData()[cellNum] instanceof Double) {
						// 数字类型
						xh.setCellValue((double) xlsDto.getData()[cellNum]);
						xh.setCellStyle(cellStyle);
					} else {
						// 字符串类型
						xh.setCellValue(xlsDto.getData()[cellNum] + "");
						xh.setCellStyle(cellStyle);
					}
				}
			}
		}
		String fileName = DateUtils.getDate("yyyyMMdd_hhmmss") + ".xls";
		response.setContentType("application/x-download");
		response.setHeader("Content-Disposition",
				"attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
		ServletOutputStream outputStream = response.getOutputStream();
		BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);
		hwb.write(bufferedOutputStream);
		bufferedOutputStream.flush();
		bufferedOutputStream.close();
		outputStream.flush();
		outputStream.close();
		hwb.close();
		return fileName;
	}

	/**
	 * 将List对象转化为 ExcelDto
	 * 
	 * @return List<ExcelDto>对象
	 * @throws IOException
	 *             输入/输出(i/o)异常
	 */
	public static List<ExcelDto> objects2ExcelDtos(List<Object> objects) throws IOException {
		List<ExcelDto> list = new ArrayList<ExcelDto>();
		int rowNumCount = objects.size();
		// 获取title
		String title = "";
		boolean titleStatusFlag = false;
		// 循环行Row
		for (int rowNum = 0; rowNum < rowNumCount; rowNum++) {
			// 获取传来的对象数据
			String o = objects.get(rowNum).toString();
			// 获取 对象属性数据对
			String[] entrys = o.split("\\|");
			// 创建对应的ExcelRow 数据对象
			ExcelDto excelRowDto = new ExcelDto((short) entrys.length);
			// 获取当前的 行 Cell 的所有列 Row 数据
			for (int cellNum = 0; cellNum < entrys.length; cellNum++) {
				String entry = entrys[cellNum];
				String[] titleAndData = entry.split("=");
				if (!titleStatusFlag) {
					System.out.println(titleAndData[0]);
					// title
					title += titleAndData[0] + "|";
				}
				// data
				excelRowDto.getData()[cellNum] = titleAndData[1];
			}
			// 代表只是执行了一次
			titleStatusFlag = true;
			list.add(excelRowDto);
		}
		setTitle(title.substring(0, title.length() - 1));
		return list;
	}

	public String getTitle() {
		return title;
	}

	/** 设置标题 */
	public static void setTitle(String titleName) {
		title = titleName;
	}

	public String getSheetName() {
		return sheetName;
	}

	/** 设置sheet名 */
	public static void setSheetName(String sheet) {
		sheetName = sheet;
	}
}
